package com.leo.service;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.leo.domain.Student;

public class ReadExcle {
	public List<Student> readXLs() throws IOException, ParseException{
		InputStream stream = new FileInputStream("./test.xls");
		HSSFWorkbook workbook = new HSSFWorkbook(stream);
		List<Student> students = new ArrayList<Student>();
		
		for(int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++){
			HSSFSheet sheet = workbook.getSheetAt(sheetNum);
			if(sheet == null){
				continue;
			}
			
			for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
				HSSFRow row = sheet.getRow(rowNum);
				if(row == null){
					continue;
				}
				
				Student student = new Student();
				
				//TODO
				//分开写法
				HSSFCell cell = row.getCell(0);
				String cellValue = getValue(cell).substring(0, getValue(cell).length()-2);
				student.setId(Integer.parseInt(cellValue));
				//合起来写了
				student.setName(getValue((row.getCell(1))));
				student.setAge(Integer.valueOf(getValue(row.getCell(2)).substring(0,getValue(row.getCell(2)).length()-2)));
				student.setBirth(new SimpleDateFormat("yyyy-MM-dd").parse(getValue(row.getCell(3))));
				
				students.add(student);
			}
		}
		
		return students;
	}
	
	/**
	 * 根据单元格的值的类型获取它的值
	 * @param hssfCell
	 * @return
	 */
	private String getValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型的值
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            // 返回字符串类型的值
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }
}
